--------------------------------------------------------------------------------
-- Retention Settings Table
--------------------------------------------------------------------------------
CREATE TABLE RA$RetentionSettings (
  retentionID      NUMBER(10),
  retentionUnit    VARCHAR2(8),
  retentionMeasure NUMBER(3),
  CONSTRAINT RA$RetentionSettings_pk PRIMARY KEY (retentionID),
  CONSTRAINT retSet_retentionUnit_ck CHECK (retentionUnit IN ('Versions','Years','Months','Days','Forever')),
  CONSTRAINT retSet_retentionMeasure_ck CHECK (retentionMeasure >= 0)
);

CREATE SEQUENCE RA$SettingsID_Seq
MINVALUE 1 MAXVALUE 9999999999
NOCACHE NOCYCLE;

CREATE OR REPLACE TRIGGER RA$RetentionSettings_ins_trg
BEFORE INSERT ON RA$RetentionSettings
FOR EACH ROW
WHEN (new.retentionID IS NULL)
BEGIN
  :new.retentionID := RA$SettingsID_Seq.NEXTVAL;
END;
/

ALTER TABLE RA$Folders
ADD retentionID NUMBER(10);

ALTER TABLE RA$Folders
ADD CONSTRAINT fldrs_retentionID_fk FOREIGN KEY (retentionID)
  REFERENCES RA$RetentionSettings(retentionID);

ALTER TABLE RA$Pages
ADD retentionID NUMBER(10);

ALTER TABLE RA$Pages
ADD CONSTRAINT pages_retentionID_fk FOREIGN KEY (retentionID)
  REFERENCES RA$RetentionSettings(retentionID);

DECLARE
  v_RetentionID RA$RetentionSettings.retentionID%TYPE;
BEGIN
  FOR v_Ret IN (
    SELECT pageID, retentionUnit, retentionMeasure
      FROM RA$Pages
     WHERE retentionUnit IS NOT NULL
  ) LOOP
    INSERT INTO RA$RetentionSettings (
      retentionUnit, retentionMeasure
    ) VALUES (
      v_Ret.retentionUnit, v_Ret.retentionMeasure
    ) RETURNING retentionID INTO v_RetentionID;
    
    UPDATE RA$Pages
       SET retentionID = v_RetentionID
     WHERE pageID = v_Ret.pageID;
  END LOOP;
END;
/

ALTER TABLE RA$Pages
DROP COLUMN retentionUnit;

ALTER TABLE RA$Pages
DROP COLUMN retentionMeasure;

--------------------------------------------------------------------------------
-- Page/Folder Objects View
--------------------------------------------------------------------------------
CREATE SEQUENCE RA$ObjectID_Seq
MINVALUE 1 MAXVALUE 9999999999
NOCACHE NOCYCLE;

DROP SEQUENCE RA$FolderID_seq;
DROP SEQUENCE RA$PageID_seq;

ALTER TABLE RA$Folders MODIFY CONSTRAINT FLDRS_PARENTFOLDERID_FK DISABLE;
ALTER TABLE RA$Pages MODIFY CONSTRAINT PAGES_FOLDERID_FK DISABLE;
DECLARE
  v_Counter  PLS_INTEGER := 0;
  v_FolderID RA$Folders.folderID%TYPE;
BEGIN
  FOR v_f IN (
    SELECT folderID FROM RA$Folders
  ) LOOP
    v_FolderID := RA$ObjectID_seq.NEXTVAL;
    UPDATE RA$Folders SET folderID = v_FolderID WHERE folderID = v_f.folderID;
    UPDATE RA$Folders SET parentFolderID = v_FolderID WHERE parentFolderID = v_f.folderID;
    UPDATE RA$Pages SET folderID = v_FolderID WHERE folderID = v_f.folderID;
    v_Counter := v_Counter + 1;
    IF MOD(v_Counter, 100) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;
/
ALTER TABLE RA$Folders MODIFY CONSTRAINT FLDRS_PARENTFOLDERID_FK ENABLE;
ALTER TABLE RA$Pages MODIFY CONSTRAINT PAGES_FOLDERID_FK ENABLE;

CREATE OR REPLACE TRIGGER RA$Folders_ins_trg
BEFORE INSERT ON RA$Folders
FOR EACH ROW
WHEN (new.folderID IS NULL)
BEGIN
  :new.folderID := RA$ObjectID_Seq.NEXTVAL;
END;
/

ALTER TABLE RA$PageRevisions MODIFY CONSTRAINT PAGEREVISIONS_PAGEID_FK DISABLE;
ALTER TABLE RA$PageLinks MODIFY CONSTRAINT PAGELINKS_LINKPAGEID_FK DISABLE;
ALTER TABLE RA$PageLinks MODIFY CONSTRAINT PAGELINKS_PAGEID_FK DISABLE;
DECLARE
  v_Counter PLS_INTEGER := 0;
  v_PageID  RA$Pages.pageID%TYPE;
BEGIN
  FOR v_p IN (
    SELECT pageID FROM RA$Pages
  ) LOOP
    v_PageID := RA$ObjectID_seq.NEXTVAL;
    UPDATE RA$Pages SET pageID = v_PageID WHERE pageID = v_p.pageID;
    UPDATE RA$PageRevisions SET pageID = v_PageID WHERE pageID = v_p.pageID;
    UPDATE RA$PageLinks SET pageID = v_PageID WHERE pageID = v_p.pageID;
    UPDATE RA$PageLinks SET linkPageID = v_PageID WHERE linkPageID = v_p.pageID;
    v_Counter := v_Counter + 1;
    IF MOD(v_Counter, 100) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;
/
ALTER TABLE RA$PageRevisions MODIFY CONSTRAINT PAGEREVISIONS_PAGEID_FK ENABLE;
ALTER TABLE RA$PageLinks MODIFY CONSTRAINT PAGELINKS_LINKPAGEID_FK ENABLE;
ALTER TABLE RA$PageLinks MODIFY CONSTRAINT PAGELINKS_PAGEID_FK ENABLE;

CREATE OR REPLACE TRIGGER RA$Pages_ins_trg
BEFORE INSERT ON RA$Pages
FOR EACH ROW
WHEN (new.pageID IS NULL)
BEGIN
  :new.pageID := RA$ObjectID_seq.NEXTVAL;
END;
/

CREATE OR REPLACE VIEW RA$Objects AS
SELECT FolderID AS objectID, FolderName AS objectName,
       parentFolderID AS parentObjectID, 'Folder' AS objectType,
       permissionSetID, retentionID
  FROM RA$Folders
UNION ALL
SELECT PageID AS objectID, PageName AS objectName,
       folderID AS parentObjectID, 'Page' AS objectType,
       permissionSetID, retentionID
  FROM RA$Pages;